SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = 'fnGetClubnameByUser')
	DROP FUNCTION fnGetClubnameByUser
GO

CREATE FUNCTION fnGetClubnameByUser
(
	@UFGUsername varchar(50)	
)
RETURNS varchar(20)
AS
BEGIN
	-- Declare the return variable here	
	DECLARE @ResultVar varchar(20)
	
	IF EXISTS (SELECT uir_UserName FROM dbo.tblUFGUsersInRoles WHERE (uir_RoleName = 'UFGAdministrator' OR uir_RoleName LIKE '% Bond') AND uir_UserName = @UFGUsername)
	BEGIN
		SET @ResultVar = '*'
	END
	ELSE
	BEGIN	
		SELECT @ResultVar = prs_FamilyClubName 
		FROM tblPersons
		WHERE prs_Username = @UFGUsername
	END
	RETURN @ResultVar
END
GO

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = 'fnGetSuspensions')
	DROP FUNCTION fnGetSuspensions
GO
CREATE FUNCTION fnGetSuspensions
(
	@PersonID int,
	@MatchDate date	,
	@Season varchar(20)
)
RETURNS INT
AS
BEGIN

	DECLARE @TEAM varchar(40)
	IF NOT EXISTS(SELECT 1 FROM dbo.tblTeamMembers
		WHERE tmm_PersonID = @PersonID AND tmm_Season = @Season)
	BEGIN
		RETURN CAST ('Invalid information about teammember' as int)
		--Work-a-round voor gooien van errors in functies
	END
	
	SELECT @TEAM = tmm_Clubname+tmm_TeamName FROM dbo.tblTeamMembers
		WHERE tmm_PersonID = @PersonID AND tmm_Season = @Season	

	DECLARE @TotalNrOfSuspension int
	SET @TotalNrOfSuspension = 0
	DECLARE @NrOfMatchesPlayed int
	SET @NrOfMatchesPlayed = 0

	DECLARE @SuspensionTotals TABLE
	(
		NrOfSuspensionsToGo int
	)
	INSERT INTO @SuspensionTotals VALUES (0) --DEFAULT
	
	INSERT INTO @SuspensionTotals
	SELECT 
		crd_NrOfSuspension - 	
		-- HET AANTAL WEDSTRIJDEN GESPEELD DOOR TEAM NA KAART DATUM, MAAR VOOR SPEELDATUM
		(SELECT COUNT(1) FROM tblMatches AS MAT 
		WHERE @TEAM IN (MAT.mat_HomeClubname+MAT.mat_HomeTeamName, MAT.mat_AgainstClubname+MAT.mat_AgainstTeamName) AND
			MAT.mat_Date > CRD.mat_Date AND MAT.mat_Date < @MatchDate AND @PersonID NOT IN 
			-- BEHALVE WEDSTRIJDEN WAAR DE SPELER TOCH OPGESTELD IS GEWEEST
			(SELECT mpl_PersonID FROM dbo.tblMatchPlayers AS MPL
				WHERE MPL.mpl_HomeSeason = MAT.mat_HomeSeason
				AND MPL.mpl_HomeTeamName = MAT.mat_HomeTeamName
				AND MPL.mpl_HomeClubname = MAT.mat_HomeClubname
				AND MPL.mpl_AgainstSeason = MAT.mat_AgainstSeason
				AND MPL.mpl_AgainstClubname = MAT.mat_AgainstClubname
				AND MPL.mpl_AgainstTeamName = MAT.mat_AgainstTeamName))
		AS NrOfSuspensions
		FROM tblCards
		JOIN tblMatches AS CRD ON 
			crd_HomeSeason = CRD.mat_HomeSeason AND
			crd_HomeClubname = CRD.mat_HomeClubname AND
			crd_HomeTeamName = CRD.mat_HomeTeamName AND
			crd_AgainstSeason = CRD.mat_AgainstSeason AND
			crd_AgainstClubname = CRD.mat_AgainstClubname AND
			crd_AgainstTeamName = CRD.mat_AgainstTeamName
	WHERE crd_PersonID = @PersonID AND crd_NrOfSuspension > 

	-- HET AANTAL WEDSTRIJDEN GESPEELD DOOR TEAM NA KAART DATUM, MAAR VOOR SPEELDATUM
	(SELECT COUNT(1) FROM tblMatches AS MAT 
		WHERE @TEAM IN (MAT.mat_HomeClubname+MAT.mat_HomeTeamName, MAT.mat_AgainstClubname+MAT.mat_AgainstTeamName) AND
			MAT.mat_Date > CRD.mat_Date AND 
			MAT.mat_Date < @MatchDate AND @PersonID NOT IN 
			-- BEHALVE WEDSTRIJDEN WAAR DE SPELER TOCH OPGESTELD IS GEWEEST -> Bv wanneer de bond achter loopt met administratie van schorsingen
			(SELECT mpl_PersonID FROM dbo.tblMatchPlayers AS MPL
				WHERE MPL.mpl_HomeSeason = MAT.mat_HomeSeason
				AND MPL.mpl_HomeTeamName = MAT.mat_HomeTeamName
				AND MPL.mpl_HomeClubname = MAT.mat_HomeClubname
				AND MPL.mpl_AgainstSeason = MAT.mat_AgainstSeason
				AND MPL.mpl_AgainstClubname = MAT.mat_AgainstClubname
				AND MPL.mpl_AgainstTeamName = MAT.mat_AgainstTeamName))


	DECLARE @RESULT int		
	SELECT @RESULT = SUM(NrOfSuspensionsToGo) FROM @SuspensionTotals
	RETURN @RESULT
END		
GO

ALTER TABLE tblTeamMembers
ADD tmm_Suspension AS  (CONVERT(INT, dbo.fnGetSuspensions(tmm_PersonID, getdate(), tmm_Season)))
GO